package com.qingneng.budget.acegi;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import javax.sql.DataSource;

import org.acegisecurity.GrantedAuthority;
import org.acegisecurity.GrantedAuthorityImpl;
import org.acegisecurity.userdetails.UsernameNotFoundException;
import org.acegisecurity.userdetails.jdbc.JdbcDaoImpl;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.MappingSqlQuery;

import com.qingneng.budget.security.IUserDetails;
import com.qingneng.budget.security.UserDetailsImpl;

/**
 * The class AcegiJdbcDaoImpl provides the method to get IUserDetail information from db which contains userId, companyId and UserDetail information.
 * 
 * @author wade
 *
 */
public class AcegiJdbcDaoImpl extends JdbcDaoImpl {
	 public static final String DEF_USERS_BY_USERNAME_QUERY =
         "SELECT USERACCOUNT, USERPASSWORD FROM USERINFO WHERE USERACCOUNT = ?";
	 public static final String DEF_AUTHORITIES_BY_USERNAME_QUERY =
         "SELECT userAccount,authority FROM USERINFO u, authorities a,user_auth ua WHERE u.id=ua.user_id and a.auth_id=ua.auth_id and u.USERACCOUNT = ?";

	protected MappingSqlQuery rolesByUsernameMapping;
	protected MappingSqlQuery usersByNameMapping;
	    
	private String authoritiesByUsernameQuery;
    private String rolePrefix = "";
    private String usersByUsernameQuery;
    private boolean usernameBasedPrimaryKey = true;
    
    public AcegiJdbcDaoImpl(){
    	usersByUsernameQuery = DEF_USERS_BY_USERNAME_QUERY;
        authoritiesByUsernameQuery = DEF_AUTHORITIES_BY_USERNAME_QUERY;
    }
    public String getAuthoritiesByUsernameQuery() {
        return authoritiesByUsernameQuery;
    }

    public String getRolePrefix() {
        return rolePrefix;
    }

    public String getUsersByUsernameQuery() {
        return usersByUsernameQuery;
    }

    protected void initMappingSqlQueries() {
        this.usersByNameMapping = new UsersByUsernameMapping(getDataSource());
        this.rolesByUsernameMapping = new AuthoritiesByUsernameMapping(getDataSource());
    }

    /**
     * Allows the default query string used to retrieve authorities based on username to be overriden, if
     * default table or column names need to be changed. The default query is {@link
     * #DEF_AUTHORITIES_BY_USERNAME_QUERY}; when modifying this query, ensure that all returned columns are mapped
     * back to the same column names as in the default query.
     *
     * @param queryString The query string to set
     */
    public void setAuthoritiesByUsernameQuery(String queryString) {
        authoritiesByUsernameQuery = queryString;
    }

    /**
     * Allows a default role prefix to be specified. If this is set to a non-empty value, then it is
     * automatically prepended to any roles read in from the db. This may for example be used to add the
     * <code>ROLE_</code> prefix expected to exist in role names (by default) by some other Acegi Security framework
     * classes, in the case that the prefix is not already present in the db.
     *
     * @param rolePrefix the new prefix
     */
    public void setRolePrefix(String rolePrefix) {
        this.rolePrefix = rolePrefix;
    }

    /**
     * If <code>true</code> (the default), indicates the {@link #getUsersByUsernameQuery()} returns a username
     * in response to a query. If <code>false</code>, indicates that a primary key is used instead. If set to
     * <code>true</code>, the class will use the database-derived username in the returned <code>UserDetailsImpl</code>.
     * If <code>false</code>, the class will use the {@link #loadUserByUsername(String)} derived username in the
     * returned <code>UserDetailsImpl</code>.
     *
     * @param usernameBasedPrimaryKey <code>true</code> if the mapping queries return the username <code>String</code>,
     *        or <code>false</code> if the mapping returns a database primary key.
     */
    public void setUsernameBasedPrimaryKey(boolean usernameBasedPrimaryKey) {
        this.usernameBasedPrimaryKey = usernameBasedPrimaryKey;
    }

    /**
     * Allows the default query string used to retrieve users based on username to be overriden, if default
     * table or column names need to be changed. The default query is {@link #DEF_USERS_BY_USERNAME_QUERY}; when
     * modifying this query, ensure that all returned columns are mapped back to the same column names as in the
     * default query. If the 'enabled' column does not exist in the source db, a permanent true value for this column
     * may be returned by using a query similar to <br><pre>
     * "SELECT username,password,'true' as enabled FROM users WHERE username = ?"</pre>
     *
     * @param usersByUsernameQueryString The query string to set
     */
    public void setUsersByUsernameQuery(String usersByUsernameQueryString) {
        this.usersByUsernameQuery = usersByUsernameQueryString;
    }
    
    public IUserDetails loadUserByUsername(String useraccount)
        throws UsernameNotFoundException, DataAccessException {
    	
        List users = usersByNameMapping.execute(useraccount);

        if (users.size() == 0) {
            throw new UsernameNotFoundException("User not found");
        }

        IUserDetails user = (IUserDetails) users.get(0); // contains no GrantedAuthority[]

        List dbAuths = rolesByUsernameMapping.execute(user.getUseraccount());

        addCustomAuthorities(user.getUseraccount(), dbAuths);

        if (dbAuths.size() == 0) {
            throw new UsernameNotFoundException("User has no GrantedAuthority");
        }

        GrantedAuthority[] arrayAuths = (GrantedAuthority[]) dbAuths.toArray(new GrantedAuthority[dbAuths.size()]);

        user.setAuthorities(arrayAuths);

        if (!usernameBasedPrimaryKey) {
            user.setUseraccount(useraccount);
        }

        return user;
    }
    
    /**
     * Query object to look up a user's authorities.
     */
    protected class AuthoritiesByUsernameMapping extends MappingSqlQuery {
        protected AuthoritiesByUsernameMapping(DataSource ds) {
            super(ds, authoritiesByUsernameQuery);
            declareParameter(new SqlParameter(Types.VARCHAR));
            compile();
        }

        protected Object mapRow(ResultSet rs, int rownum)
            throws SQLException {
            String roleName = rolePrefix + rs.getString(2);
            GrantedAuthorityImpl authority = new GrantedAuthorityImpl(roleName);

            return authority;
        }
    }

    /**
     * Query object to look up a user.
     */
    protected class UsersByUsernameMapping extends MappingSqlQuery {
        protected UsersByUsernameMapping(DataSource ds) {
            super(ds, usersByUsernameQuery);
            declareParameter(new SqlParameter(Types.VARCHAR));
            compile();
        }

        protected Object mapRow(ResultSet rs, int rownum)
            throws SQLException {
        	int user_id = rs.getInt(1);
        	String useraccount = rs.getString(2);
            String userpassword = rs.getString(3);
            String username = rs.getString(4);
            String comanycode = rs.getString(5);
            String comanyname = rs.getString(6);
           
            IUserDetails user = new UserDetailsImpl(useraccount,userpassword,true,true,true,true,
            		new GrantedAuthority[] {new GrantedAuthorityImpl("HOLDER")});
            
            user.setUseraccount(useraccount);
            user.setCompanyCode(comanycode);
            user.setCompanyName(comanyname);
            user.setUsername(username);
            user.setUserpassword(userpassword);
            return user;
        }
    }
}
